﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Core.Data.MsSqlServer
{
    public class SqlServer
    {
        #region Properties
        public SqlConnection Connection { get; set; }
        public SqlCommand Command { get; set; }
        public SqlDataAdapter DataAdapter { get; set; }
        public List<SqlServerParameter> ParameterCollection { get; set; }
        #endregion

        #region Constructor
        public SqlServer()
        {
            ParameterCollection = new List<SqlServerParameter>();
        }
        public SqlServer(string connectionString)
        {
            Connection = new SqlConnection(connectionString);
            ParameterCollection = new List<SqlServerParameter>();
        }
        public SqlServer(SqlConnection connection)
        {
            Connection = connection;
            ParameterCollection = new List<SqlServerParameter>();
        }
        #endregion

        #region Method
        public virtual void Open()
        {
            try
            {
                if (Connection.State == ConnectionState.Open) Connection.Close();
                Connection.Open();
            }
            catch (Exception ex) { throw new Exception("Connection cannot open.", ex); }
        }
        public virtual void Close()
        {
            try
            {
                Connection.Close();
            }
            catch (Exception ex) { throw new Exception("Connection cannot close.", ex); }
        }
        public virtual bool ExecuteNonQuery(string query)
        {
            try
            {
                Open();
                Command = new SqlCommand(query);
                Command.Connection = Connection;
                int retValue = Command.ExecuteNonQuery();
                if (retValue <= 0) { return false; }
                return true;
            }
            catch (Exception ex) { throw new Exception("SqlServer cannot excute query.", ex); }
            finally { Close(); }
        }
        public virtual bool ExecuteNonQuery(string query, out DataSet dataSet)
        {
            try
            {
                Open();
                Command = new SqlCommand(query);
                Command.Connection = Connection;

                DataAdapter = new SqlDataAdapter(Command);
                dataSet = new DataSet();
                DataAdapter.Fill(dataSet);

                if (dataSet.Tables[0].Rows.Count == 0)
                {
                    dataSet = null;
                    return false;
                }
                return true;
            }
            catch (Exception ex) { throw new Exception("SqlServer cannot excute query.", ex); }
            finally { Close(); }
        }
        public virtual bool ExecuteNonQuery(string query, List<SqlServerParameter> parameters)
        {
            try
            {
                Open();
                Command = new SqlCommand(query);
                Command.Connection = Connection;
                Command.Parameters.Clear();
                foreach (SqlServerParameter parameter in parameters)
                {
                    Command.Parameters.AddWithValue(parameter.ParameterName, parameter.ParameterValue);
                }

                int retValue = Command.ExecuteNonQuery();
                if (retValue <= 0) { return false; }
                return true;
            }
            catch (Exception ex) { throw new Exception("SqlServer cannot excute query.", ex); }
            finally { Close(); }
        }
        public virtual bool ExecuteNonQuery(string query, List<SqlServerParameter> parameters, out DataSet dataSet)
        {
            try
            {
                Open();
                Command = new SqlCommand(query);
                Command.Connection = Connection;

                foreach (SqlServerParameter parameter in parameters)
                {
                    Command.Parameters.AddWithValue(parameter.ParameterName, parameter.ParameterValue);
                }

                DataAdapter = new SqlDataAdapter(Command);
                dataSet = new DataSet();
                DataAdapter.Fill(dataSet);

                if (dataSet.Tables[0].Rows.Count == 0)
                {
                    dataSet = null;
                    return false;
                }
                return true;
            }
            catch (Exception ex) { throw new Exception("SqlServer cannot excute query.", ex); }
            finally { Close(); }
        }
        public virtual bool ExecuteNonQuery(string query, CommandType commandType)
        {
            try
            {
                Open();
                Command = new SqlCommand(query);
                Command.Connection = Connection;
                Command.CommandType = commandType;
                int retValue = Command.ExecuteNonQuery();
                if (retValue <= 0) { return false; }
                return true;
            }
            catch (Exception ex) { throw new Exception("SqlServer cannot excute query.", ex); }
            finally { Close(); }
        }
        public virtual bool ExecuteNonQuery(string query, CommandType commandType, out DataSet dataSet)
        {
            try
            {
                Open();
                Command = new SqlCommand(query);
                Command.Connection = Connection;
                Command.CommandType = commandType;
                DataAdapter = new SqlDataAdapter(Command);
                dataSet = new DataSet();
                DataAdapter.Fill(dataSet);

                if (dataSet.Tables[0].Rows.Count == 0)
                {
                    dataSet = null;
                    return false;
                }
                return true;
            }
            catch (Exception ex) { throw new Exception("SqlServer cannot excute query.", ex); }
            finally { Close(); }
        }
        public virtual bool ExecuteNonQuery(string query, CommandType commandType, List<SqlServerParameter> parameters)
        {
            try
            {
                Open();
                Command = new SqlCommand(query);
                Command.Connection = Connection;
                Command.CommandType = commandType;
                Command.Parameters.Clear();
                foreach (SqlServerParameter parameter in parameters)
                {
                    Command.Parameters.AddWithValue(parameter.ParameterName, parameter.ParameterValue);
                }
                int retValue = Command.ExecuteNonQuery();
                if (retValue <= 0) { return false; }
                return true;
            }
            catch (Exception ex) { throw new Exception("SqlServer cannot excute query.", ex); }
            finally { Close(); }
        }
        public virtual bool ExecuteNonQuery(string query, CommandType commandType, List<SqlServerParameter> parameters, out DataSet dataSet)
        {
            try
            {
                Open();
                Command = new SqlCommand(query);
                Command.Connection = Connection;
                Command.CommandType = commandType;
                
                foreach (SqlServerParameter parameter in parameters)
                {
                    Command.Parameters.AddWithValue(parameter.ParameterName, parameter.ParameterValue);
                }

                DataAdapter = new SqlDataAdapter(Command);
                dataSet = new DataSet();
                DataAdapter.Fill(dataSet);

                if (dataSet.Tables[0].Rows.Count == 0)
                {
                    dataSet = null;
                    return false;
                }
                return true;
            }
            catch (Exception ex) { throw new Exception("SqlServer cannot excute query.", ex); }
            finally { Close(); }
        }
        public virtual object ExecuteScalar(string query)
        {
            try
            {
                Open();
                Command = new SqlCommand(query);
                Command.Connection = Connection;
                object retValue = Command.ExecuteScalar();
                if (retValue == null) return null;
                return retValue;
            }
            catch (Exception ex) { throw new Exception("SqlServer cannot excute query.", ex); }
            finally { Close(); }
        }
        public virtual object ExecuteScalar(string query, CommandType commandType)
        {
            try
            {
                Open();
                Command = new SqlCommand(query);
                Command.Connection = Connection;
                Command.CommandType = commandType;

                object retValue = Command.ExecuteScalar();
                if (retValue == null) return null;
                return retValue;
            }
            catch (Exception ex) { throw new Exception("SqlServer cannot excute query.", ex); }
            finally { Close(); }
        }
        public virtual object ExecuteScalar(string query, CommandType commandType, List<SqlServerParameter> parameters)
        {
            try
            {
                Open();
                Command = new SqlCommand(query);
                Command.Connection = Connection;
                Command.CommandType = commandType;
                foreach (SqlServerParameter parameter in parameters)
                {
                    Command.Parameters.AddWithValue(parameter.ParameterName, parameter.ParameterValue);
                }

                object retValue = Command.ExecuteScalar();
                if (retValue == null) return null;
                return retValue;
            }
            catch (Exception ex) { throw new Exception("SqlServer cannot excute query.", ex); }
            finally { Close(); }
        }
        public virtual object ExecuteScalar(string query, List<SqlServerParameter> parameters)
        {
            try
            {
                Open();
                Command = new SqlCommand(query);
                Command.Connection = Connection;

                foreach (SqlServerParameter parameter in parameters)
                {
                    Command.Parameters.AddWithValue(parameter.ParameterName, parameter.ParameterValue);
                }

                object retValue = Command.ExecuteScalar();
                if (retValue == null) return null;
                return retValue;
            }
            catch (Exception ex) { throw new Exception("SqlServer cannot excute query.", ex); }
            finally { Close(); }
        }
        
        #endregion
    }
}
